package com.campusbackend.mapper;

import com.campusbackend.entity.Students;
import com.campusbackend.entity.vo.StudentInfoVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 学生用户表 Mapper 接口
 * </p>
 *
 * @author yezi
 * @since 2025-07-24
 */
@Mapper
public interface StudentsMapper extends BaseMapper<Students> {

    /**
     * 根据学号查找学生
     * @param studentId 学号
     * @return 学生信息
     */
    @Select("SELECT * FROM students WHERE student_id = #{studentId}")
    Students findByStudentId(String studentId);

    /**
     * 智能推荐学生（随机查询8名符合条件的学生）
     * @param college 学院
     * @return 学生列表
     */
    @Select("SELECT * FROM students WHERE college = #{college} AND audit_status = 1 AND status = 1 ORDER BY RAND() LIMIT 8")
    List<Students> recommendStudents(@Param("college") String college);

    /**
     * 精准搜索学生
     * @param college 学院
     * @param keyword 关键词（姓名或学号）
     * @return 学生列表
     */
    @Select("<script>" +
            "SELECT * FROM students WHERE college = #{college} AND audit_status = 1 AND status = 1" +
            "<if test='keyword != null and keyword != \"\"'>" +
            " AND (real_name LIKE CONCAT('%', #{keyword}, '%') OR student_id LIKE CONCAT('%', #{keyword}, '%'))" +
            "</if>" +
            "</script>")
    List<Students> searchStudents(@Param("college") String college, @Param("keyword") String keyword);

    /**
     * 检查学号是否已存在
     * @param studentId 学号
     * @return 存在的记录数
     */
    @Select("SELECT COUNT(*) FROM students WHERE student_id = #{studentId}")
    int countByStudentId(@Param("studentId") String studentId);

    /**
     * 检查邮箱是否已存在
     * @param email 邮箱
     * @return 存在的记录数
     */
    @Select("SELECT COUNT(*) FROM students WHERE email = #{email}")
    int countByEmail(@Param("email") String email);

    /**
     * 检查邮箱是否被其他学生占用（排除当前学生）
     * @param email 邮箱
     * @param studentId 当前学生学号
     * @return 存在的记录数
     */
    @Select("SELECT COUNT(*) FROM students WHERE email = #{email} AND student_id != #{studentId}")
    int countByEmailExcludeCurrentUser(@Param("email") String email, @Param("studentId") String studentId);

    /**
     * 检查手机号是否已存在
     * @param phone 手机号
     * @return 存在的记录数
     */
    @Select("SELECT COUNT(*) FROM students WHERE phone = #{phone}")
    int countByPhone(@Param("phone") String phone);

    /**
     * 检查手机号是否被其他学生占用（排除当前学生）
     * @param phone 手机号
     * @param studentId 当前学生学号
     * @return 存在的记录数
     */
    @Select("SELECT COUNT(*) FROM students WHERE phone = #{phone} AND student_id != #{studentId}")
    int countByPhoneExcludeCurrentUser(@Param("phone") String phone, @Param("studentId") String studentId);

    /**
     * 检查身份证号是否已存在
     * @param idCard 身份证号
     * @return 存在的记录数
     */
    @Select("SELECT COUNT(*) FROM students WHERE id_card = #{idCard}")
    int countByIdCard(@Param("idCard") String idCard);

    /**
     * 检查身份证号是否被其他学生占用（排除当前学生）
     * @param idCard 身份证号
     * @param studentId 当前学生学号
     * @return 存在的记录数
     */
    @Select("SELECT COUNT(*) FROM students WHERE id_card = #{idCard} AND student_id != #{studentId}")
    int countByIdCardExcludeCurrentUser(@Param("idCard") String idCard, @Param("studentId") String studentId);

    /**
     * 获取待审核的学生列表
     * @return 待审核学生列表
     */
    @Select("SELECT * FROM students WHERE audit_status = 0 ORDER BY create_time DESC")
    List<Students> getPendingAuditStudents();

    /**
     * 获取所有学生（用于管理）
     * @return 所有学生列表
     */
    @Select("SELECT * FROM students WHERE audit_status != 0 ORDER BY create_time DESC")
    List<Students> getAllStudentsForManagement();

    /**
     * 根据审核状态统计学生数量
     * @param auditStatus 审核状态
     * @return 学生数量
     */
    @Select("SELECT COUNT(*) FROM students WHERE audit_status = #{auditStatus}")
    long countByAuditStatus(@Param("auditStatus") Byte auditStatus);

    /**
     * 根据账号状态统计学生数量
     * @param status 账号状态
     * @return 学生数量
     */
    @Select("SELECT COUNT(*) FROM students WHERE status = #{status}")
    long countByStatus(@Param("status") Byte status);

    /**
     * 统计今日新增学生数量
     * @return 今日新增学生数量
     */
    @Select("SELECT COUNT(*) FROM students WHERE DATE(create_time) = CURDATE()")
    long countTodayNew();

    /**
     * 统计本月新增学生数量
     * @return 本月新增学生数量
     */
    @Select("SELECT COUNT(*) FROM students WHERE YEAR(create_time) = YEAR(NOW()) AND MONTH(create_time) = MONTH(NOW())")
    long countMonthNew();

    /**
     * 更新学生审核状态
     * @param id 学生ID
     * @param auditStatus 审核状态
     * @param auditReason 审核备注
     * @return 更新的记录数
     */
    @Update("UPDATE students SET audit_status = #{auditStatus}, audit_reason = #{auditReason}, audit_time = NOW() WHERE id = #{id}")
    int updateAuditStatus(@Param("id") Long id, @Param("auditStatus") Byte auditStatus, @Param("auditReason") String auditReason);

    /**
     * 更新学生审核状态和账号状态
     * @param id 学生ID
     * @param auditStatus 审核状态
     * @param status 账号状态
     * @param auditReason 审核备注
     * @return 更新的记录数
     */
    @Update("UPDATE students SET audit_status = #{auditStatus}, status = #{status}, audit_reason = #{auditReason}, audit_time = NOW(), update_time = NOW() WHERE id = #{id}")
    int updateAuditAndAccountStatus(@Param("id") Long id, @Param("auditStatus") Byte auditStatus, @Param("status") Byte status, @Param("auditReason") String auditReason);

    /**
     * 获取被驳回的学生列表
     * @return 被驳回学生列表
     */
    @Select("SELECT * FROM students WHERE audit_status = 2 ORDER BY audit_time DESC")
    List<Students> getRejectedStudents();

    /**
     * 获取被注销的学生列表
     * @return 被注销学生列表
     */
    @Select("SELECT * FROM students WHERE status = 0  and audit_status=1 ORDER BY update_time DESC")
    List<Students> getDeactivatedStudents();

    /**
     * 根据关键词搜索学生（用于管理功能，返回完整实体）
     * @param keyword 搜索关键词
     * @return 学生列表
     */
    @Select("SELECT * FROM students WHERE real_name LIKE CONCAT('%', #{keyword}, '%') OR student_id LIKE CONCAT('%', #{keyword}, '%') ORDER BY create_time DESC")
    List<Students> searchStudentsForManagement(@Param("keyword") String keyword);

    /**
     * 根据学号查找学生详情
     * @param studentId 学号
     * @return 学生信息
     */
    @Select("SELECT * FROM students WHERE student_id = #{studentId}")
    Students findByStudentIdForDetail(@Param("studentId") String studentId);

    /**
     * 按日期统计新增学生数量（最近N天）
     * @param days 天数
     * @return 每日新增学生统计
     */
    @Select("SELECT DATE(create_time) as date, COUNT(*) as count " +
            "FROM students " +
            "WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL #{days} DAY) " +
            "GROUP BY DATE(create_time) " +
            "ORDER BY date DESC")
    List<Map<String, Object>> getDailyNewStudentsCount(@Param("days") int days);

    /**
     * 查询学生所属的队伍列表
     * @param studentId 学生ID
     * @return 学生所属队伍的简化信息列表
     */
    @Select("SELECT " +
            "    t.id as teamId, " +
            "    t.team_name as teamName, " +
            "    t.team_code as teamCode, " +
            "    t.team_type as teamType, " +
            "    tm.member_role as memberRole, " +
            "    te.real_name as instructorName, " +
            "    t.current_members as currentMembers, " +
            "    t.max_members as maxMembers, " +
            "    tm.join_time as joinTime, " +
            "    t.status " +
            "FROM team_members tm " +
            "LEFT JOIN teams t ON tm.team_id = t.id " +
            "LEFT JOIN teachers te ON t.instructor_id = te.id " +
            "WHERE tm.student_id = #{studentId} AND tm.join_status = 1 AND t.status = 1 " +
            "ORDER BY tm.join_time DESC")
    List<StudentInfoVO.TeamSimpleInfo> getStudentTeams(@Param("studentId") Long studentId);

    /**
     * 查询学生的技能特长列表
     * @param studentId 学生ID
     * @return 学生技能的简化信息列表
     */
    @Select("SELECT " +
            "    s.id as skillId, " +
            "    s.skill_name as skillName, " +
            "    s.skill_category as skillCategory, " +
            "    s.description, " +
            "    ss.proficiency_level as proficiencyLevel, " +
            "    CASE " +
            "        WHEN ss.proficiency_level = 1 THEN '初级' " +
            "        WHEN ss.proficiency_level = 2 THEN '中级' " +
            "        WHEN ss.proficiency_level = 3 THEN '高级' " +
            "        ELSE '未知' " +
            "    END as proficiencyLevelDesc, " +
            "    ss.create_time as createTime " +
            "FROM student_skills ss " +
            "LEFT JOIN skills s ON ss.skill_id = s.id " +
            "WHERE ss.student_id = #{studentId} AND s.status = 1 " +
            "ORDER BY ss.create_time DESC")
    List<StudentInfoVO.SkillSimpleInfo> getStudentSkills(@Param("studentId") Long studentId);
}
